/**
 * Repositorio usuario com os principais metodos
 * 
 * @author			Paulo Lima
 * @corporation 	TeckSoft 2014
 * @project 		Araujo Seguros
 * @version 		1.0 
 * @since			Release 1  24/03/2014
 */
package repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


import util.ConnectionFactory;
import models.Usuario;

public class RepositorioUsuario implements IRepositorioUsuario {

	// ---------------------------------------------------------------------------------
	public boolean inserirUsuario(Usuario usuario) throws SQLException {
		String sql = "insert into usuario values (?,?,?,?,?,?,?)";

		try {

			// conectando
			Connection conIntranet = new ConnectionFactory()
					.getConnection();

			PreparedStatement stm1 = (PreparedStatement) conIntranet
					.prepareStatement(sql);

			stm1.setInt(1, 0);
			stm1.setString(2, usuario.getSenha());
			stm1.setString(3, usuario.getNomeCompleto());
			stm1.setString(4, usuario.getHoraCadastro());
			stm1.setString(5, usuario.getLogin());
			stm1.setString(6, usuario.getDatahoraModificacao());
			stm1.setString(7, usuario.getDataCadastro());
			stm1.execute();
			stm1.close();
			conIntranet.close();
			return true;

		} catch (Exception e) {
			return false;
		}

	}

	// ---------------------------------------------------------------------------------
	public boolean removerUsuario(Usuario usuario) throws SQLException {
		String sql = "DELETE FROM usuario WHERE id=?";

		try {
			// conectando
			Connection conIntranet = new ConnectionFactory()
					.getConnection();

			PreparedStatement stm1 = (PreparedStatement) conIntranet
					.prepareStatement(sql);

			stm1.setInt(1, usuario.getId());
			stm1.execute();
			stm1.close();
			conIntranet.close();
			return true;

		} catch (Exception e) {
			return false;
		}

	}

	// ---------------------------------------------------------------------------------
	public boolean atualizarUsuario(Usuario usuario) throws SQLException {
		String sql = "UPDATE usuario SET "
				+ "senha=?, nome_completo=?, hora_cadastro=?, login=?, "
				+ "data_hora_modificacao=? WHERE id=?";

		try {
			// conectando
			Connection conIntranet = new ConnectionFactory()
					.getConnection();

			PreparedStatement stm1 = (PreparedStatement) conIntranet
					.prepareStatement(sql);

			stm1.setString(1, usuario.getSenha());
			stm1.setString(2, usuario.getNomeCompleto());
			stm1.setString(3, usuario.getHoraCadastro());
			stm1.setString(4, usuario.getLogin());
			stm1.setString(5, usuario.getDatahoraModificacao());
			stm1.setInt(6, usuario.getId());
			stm1.executeUpdate();
			stm1.close();
			conIntranet.close();
			return true;

		} catch (Exception e) {
			return false;
		}

	}

	// ---------------------------------------------------------------------------------
	public List<Usuario> listarUsuario() throws SQLException {

		ArrayList<Usuario> usuarioDB = new ArrayList<>();
		Usuario usuario;
		String sql = "SELECT * FROM usuario";

		try {

			Connection conIntranet = new ConnectionFactory()
					.getConnection();

			PreparedStatement stm1 = (PreparedStatement) conIntranet
					.prepareStatement(sql);

			ResultSet rs1 = stm1.executeQuery();

			while (rs1.next()) {

				usuario = new Usuario();
				usuario.setId(rs1.getInt(1));
				usuario.setSenha(rs1.getString(2));
				usuario.setNomeCompleto(rs1.getString(3));
				usuario.setHoraCadastro(rs1.getString(4));
				usuario.setLogin(rs1.getString(5));
				usuario.setDatahoraModificacao(rs1.getString(6));
				usuario.setDataCadastro(rs1.getString(7));
				usuarioDB.add(usuario);

			}
			rs1.close();
			stm1.close();
			conIntranet.close();

		} catch (Exception e) {
			new SQLException();
		}

		return usuarioDB;
	}

	// ---------------------------------------------------------------------------------
	public List<Usuario> buscarUsuario(String chave) throws SQLException {
		ArrayList<Usuario> usuarioDB = new ArrayList<>();
		Usuario usuario;
		String sql = "SELECT * FROM usuario WHERE login LIKE '%" + chave + "%'";

		try {

			Connection conIntranet = new ConnectionFactory()
					.getConnection();

			PreparedStatement stm1 = (PreparedStatement) conIntranet
					.prepareStatement(sql);

			ResultSet rs1 = stm1.executeQuery();

			while (rs1.next()) {

				usuario = new Usuario();
				usuario.setId(rs1.getInt(1));
				usuario.setSenha(rs1.getString(2));
				usuario.setNomeCompleto(rs1.getString(3));
				usuario.setHoraCadastro(rs1.getString(4));
				usuario.setLogin(rs1.getString(5));
				usuario.setDatahoraModificacao(rs1.getString(6));
				usuario.setDataCadastro(rs1.getString(7));
				usuarioDB.add(usuario);

			}
			rs1.close();
			stm1.close();
			conIntranet.close();

		} catch (Exception e) {
			new SQLException();

		}

		return usuarioDB;
	}

	// ---------------------------------------------------------------------------------
	public boolean autenticar(String login, String senha) throws SQLException {

		String sql = "SELECT login, senha FROM usuario";

		try {
			Connection conIntranet = new ConnectionFactory()
					.getConnection();

			PreparedStatement stm1 = (PreparedStatement) conIntranet
					.prepareStatement(sql);

			ResultSet rs1 = stm1.executeQuery();

			while (rs1.next()) {

				String loginBD = rs1.getString(1);
				String senhaBD = rs1.getString(2);

				if (loginBD.equals(login) && senhaBD.equals(senha)) {
					return true;
				}

			}
			rs1.close();
			stm1.close();
			conIntranet.close();

		} catch (Exception e) {

		new SQLException();
		}

		return false;

	}

	// ---------------------------------------------------------------------------------
	public boolean verificarLogin(String login) throws SQLException, Exception {

		String sql = "SELECT login FROM usuario";

		try {

			Connection conIntranet = new ConnectionFactory()
					.getConnection();

			PreparedStatement stm1 = (PreparedStatement) conIntranet
					.prepareStatement(sql);

			ResultSet rs1 = stm1.executeQuery();

			while (rs1.next()) {

				String loginBD = rs1.getString(1);

				if (loginBD.equals(login)) {
					return true;
				}

			}
			rs1.close();
			stm1.close();
			conIntranet.close();

		} catch (Exception e) {
			new SQLException();
		}

		return false;

	}
}
